/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package giormi;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFrame;
import javax.swing.JOptionPane;

/**
 *
 * @author Ling
 */
public class F_updateorder {
    private String o_dateposted;
    private String o_qtysold;
    private String o_datedelivered;
    private String o_drdate;
    private String o_drnum;
    private String o_wsnum;
    private String o_wrnum;
    private String o_qtyreturned;
    private String o_notes;
    private int o_idorder_stock;
    private int o_idcustomer;
    private int o_idstock;
    private int idorder;

    private int prev_o_idstock;
    private int prev_o_qtysold;
    private int prev_o_qtyreturned;
    private int curr_o_qtysold;
    private int curr_o_qtyreturned;

    private JFrame prev;

    public void setPrev(JFrame prev) {
        this.prev = prev;
    }

    public void setO_idstock(int o_idstock) {
        this.o_idstock = o_idstock;
    }

    public void setO_datedelivered(String o_datedelivered) {
        this.o_datedelivered = o_datedelivered;
    }

    public void setO_dateposted(String o_dateposted) {
        this.o_dateposted = o_dateposted;
    }

    public void setO_drdate(String o_drdate) {
        this.o_drdate = o_drdate;
    }

    public void setO_drnum(String o_drnum) {
        this.o_drnum = o_drnum;
    }

    public void setO_idcustomer(int o_idcustomer) {
        this.o_idcustomer = o_idcustomer;
    }

    public void setO_notes(String o_notes) {
        this.o_notes = o_notes;
    }

    public void setO_idorder_stock(int o_idorder_stock) {
        this.o_idorder_stock = o_idorder_stock;
    }

    public void setO_qtyreturned(String o_qtyreturned) {
        this.o_qtyreturned = o_qtyreturned;
    }

    public void setO_qtysold(String o_qtysold) {
        this.o_qtysold = o_qtysold;
    }

    public void setO_wrnum(String o_wrnum) {
        this.o_wrnum = o_wrnum;
    }

    public void setO_wsnum(String o_wsnum) {
        this.o_wsnum = o_wsnum;
    }
    
    //GETTERS
    public int getO_idstock() {
        return o_idstock;
    }

    public String getO_datedelivered() {
        return o_datedelivered;
    }

    public String getO_dateposted() {
        return o_dateposted;
    }

    public String getO_drdate() {
        return o_drdate;
    }

    public String getO_drnum() {
        return o_drnum;
    }

    public int getO_idcustomer() {
        return o_idcustomer;
    }

    public String getO_notes() {
        return o_notes;
    }

    public int getO_idorder_stock() {
        return o_idorder_stock;
    }

    public String getO_qtyreturned() {
        return o_qtyreturned;
    }

    public String getO_qtysold() {
        return o_qtysold;
    }

    public String getO_wrnum() {
        return o_wrnum;
    }

    public String getO_wsnum() {
        return o_wsnum;
    }

    private Connection conn;
    private String SQLStatement;
    private String URL;
    private Statement stmt;
    public ResultSet rset;

    public void connect(){
        try{
            DBConnection db = new DBConnection();
            conn = DBConnection.get();
            stmt = conn.createStatement();
        }
        catch(Exception e){
            System.out.println(e.getMessage());
            JOptionPane.showMessageDialog(null,e.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

    public void disconnect(){
        try{
            stmt.close();
            conn.close();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

    public int getidcustomer(String customername){
        int idcustomer = 0;
        connect();
        try {
            SQLStatement = "SELECT idcustomer FROM customer WHERE "
                    + "c_name = '" + customername + "'";
            rset = stmt.executeQuery(SQLStatement);
            if (rset.next()) {
              idcustomer = rset.getInt(1);
              System.out.println(rset.getInt(1));
            }
            rset.close();
            disconnect();
        } catch (SQLException ex) {
            Logger.getLogger(F_addstock.class.getName()).log(Level.SEVERE, null, ex);
        }
        return idcustomer;
    }
    
    public int getidorder(){
        int idorder = 0;
        connect();
        try {
            SQLStatement = "SELECT MAX(idorder) FROM orderr";
            rset = stmt.executeQuery(SQLStatement);
            if (rset.next()) {
              idorder = rset.getInt(1);
              System.out.println("idorderfromselect:"+idorder);
            }
            rset.close();
            disconnect();
        } catch (SQLException ex) {
            Logger.getLogger(F_addstock.class.getName()).log(Level.SEVERE, null, ex);
        }
        return idorder+1;
    }


    public void getorder(int idorder){
        this.idorder = idorder;
        connect();
        try{
            SQLStatement = "SELECT * FROM orderr WHERE idorder = " +idorder;
            rset = stmt.executeQuery(SQLStatement);
            if(rset.next() != false){
                idorder = rset.getInt(1);
                this.o_dateposted = rset.getString(2);
                this.o_qtysold = rset.getString(3);
                this.o_datedelivered = rset.getString(4);
                this.o_drdate= rset.getString(5);
                this.o_drnum = rset.getString(6);
                this.o_wsnum = rset.getString(7);
                this.o_wrnum = rset.getString(8);
                this.o_qtyreturned = rset.getString(9);
                this.o_notes = rset.getString(10);
                this.o_idorder_stock = rset.getInt(11);
                this.o_idcustomer = rset.getInt(12);
            }

            SQLStatement = "SELECT os_idstock FROM order_stock WHERE idorder_stock = " +o_idorder_stock;
            rset = stmt.executeQuery(SQLStatement);
            if(rset.next() != false){
                o_idstock = rset.getInt("os_idstock");
            }
            rset.close();
            disconnect();
            this.prev_o_idstock = this.o_idstock;
            this.prev_o_qtyreturned = Integer.parseInt(this.o_qtyreturned);
            this.prev_o_qtysold = Integer.parseInt(this.o_qtysold);
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }   
    
    public String getProductinfo(String type) {
        String temp = new String();
        int storeid = 0;
        
        //FIRST GET THE STOCK ID
        connect();
        try{
            SQLStatement = "SELECT os_idstock FROM order_stock WHERE idorder_stock = " + o_idorder_stock;
            rset = stmt.executeQuery(SQLStatement);
            if (rset.next() != false){
                storeid = rset.getInt("os_idstock");
            }
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
        this.o_idstock = storeid;
        System.out.println("idstock:"+o_idstock);
        
        //THEN GET THE PRODUCT ID
        connect();
         try{
            SQLStatement = "SELECT s_idproduct FROM stock WHERE idstock = " + storeid;
            rset = stmt.executeQuery(SQLStatement);
            if (rset.next() != false)
                storeid = rset.getInt(1);
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
        
        //THEN GET THE INFO OF THE PRODUCT
        connect();
        try{
            SQLStatement = "SELECT p_name, p_packing FROM product WHERE idproduct = " + storeid;
            rset = stmt.executeQuery(SQLStatement);
            if (rset.next() != false){
                if ("name".equals(type))
                    temp = rset.getString(1);
                else
                    temp = rset.getString(2);
            }
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        } 
        return temp;
    }
    
    public void updateorder(){
        System.out.println(o_idstock +"="+ prev_o_idstock);
        updatestock();

        connect();
        try {
            SQLStatement = "UPDATE orderr SET "+
                    "o_dateposted = '" + o_dateposted +
                    "', o_qtysold = " + Integer.parseInt(o_qtysold) +
                    ", o_datedelivered = '" + o_datedelivered +
                    "', o_drdate = '" + o_drdate +
                    "', o_drnum = '" + o_drnum +
                    "', o_wsnum = '" + o_wsnum +
                    "', o_wrnum = '" + o_wrnum +
                    "', o_qtyreturned = " + Integer.parseInt(o_qtyreturned) +
                    ", o_notes = '" + o_notes +
                    "', o_order_stock_id = " + o_idorder_stock +
                    ", o_idcustomer = " + o_idcustomer +
                    " WHERE idorder = " + idorder;
            stmt.executeUpdate(SQLStatement);
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

    public String getCustomer(){
        String temp = new String();
        connect();
        try{
            SQLStatement = "SELECT c_name FROM customer WHERE idcustomer = " + o_idcustomer;
            rset = stmt.executeQuery(SQLStatement);
            if (rset.next() != false)
                temp = rset.getString(1);
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
        return temp;
    }
            
    
    public void updatestock(){
        System.out.println("updatestock");
        int rbalance = 0;
        int changedorder = 0;
        connect();
        try {
            if (this.o_idstock==this.prev_o_idstock){
                SQLStatement = "SELECT s_balance FROM stock WHERE idstock=" + o_idstock;
                rset = stmt.executeQuery(SQLStatement);
                if (rset.next()) {
                    rbalance = rset.getInt(1);
                }
                System.out.println("rbalance:"+rbalance);

                curr_o_qtysold = Integer.parseInt(o_qtysold);
                curr_o_qtyreturned = Integer.parseInt(o_qtyreturned);
                changedorder = (curr_o_qtysold+curr_o_qtyreturned) -(this.prev_o_qtyreturned+this.prev_o_qtysold);
                System.out.println("changedorder="+(curr_o_qtysold+curr_o_qtyreturned)+
                        "-"+(this.prev_o_qtyreturned+this.prev_o_qtysold)+"="+changedorder);

                rbalance -= changedorder;
                System.out.println("rbalance="+rbalance+"-"+changedorder+"="+rbalance);

                if(rbalance>=0){
                SQLStatement = "UPDATE stock SET s_balance=" + rbalance + " WHERE idstock=" + o_idstock;
                stmt.executeUpdate(SQLStatement);
                }
                else {
                    warningFrame wa = new warningFrame("invaliddata", prev);
                    wa.setVisible(true);
                }
            }

             else {
                int newbalance = 0, oldbalance = 0;

                SQLStatement = "SELECT s_balance FROM stock WHERE idstock=" + prev_o_idstock; // old stock
                rset = stmt.executeQuery(SQLStatement);
                if (rset.next()) {
                    oldbalance = rset.getInt(1); // old stock balance
                }
                System.out.println("OLD stockid:"+prev_o_idstock+" balance:"+newbalance);

                SQLStatement = "SELECT s_balance FROM stock WHERE idstock=" + o_idstock; // new stock
                rset = stmt.executeQuery(SQLStatement);
                if (rset.next()) {
                    newbalance = rset.getInt(1); // new stock balance
                }
                System.out.println("NEW stockid:"+o_idstock+" balance:"+newbalance);

                curr_o_qtysold = Integer.parseInt(o_qtysold);
                curr_o_qtyreturned = Integer.parseInt(o_qtyreturned);
                
                // old stock's updated balance
                oldbalance = oldbalance + curr_o_qtysold + curr_o_qtyreturned;
                // new stock's updated balance
                newbalance = newbalance - (curr_o_qtysold + curr_o_qtyreturned);

                System.out.println("OLD stockid:"+prev_o_idstock+" balance:"+oldbalance);
                System.out.println("NEW stockid:"+o_idstock+" balance:"+newbalance);
                
                if(newbalance>=0){
                SQLStatement = "UPDATE stock SET s_balance=" + oldbalance
                        + " WHERE idstock=" + prev_o_idstock;
                stmt.executeUpdate(SQLStatement);

                SQLStatement = "UPDATE stock SET s_balance=" + newbalance
                        + " WHERE idstock=" + o_idstock;
                stmt.executeUpdate(SQLStatement);
                }
                else {
                    warningFrame wa = new warningFrame("invaliddata", prev);
                    wa.setVisible(true);
                }

             }
            disconnect();
        } catch (SQLException ex) {
            Logger.getLogger(F_updateorder.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}
